using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using eShop.Model;
using System.Collections.Generic;
using eShop.Utility;


namespace eShop.DataAccess
{
    public class CategoryDAO : DatabaseUtility
    {
        public static bool DeleteTblProductCategory
        (
            Int32 categoryId
        )
        {
            //Validate Input
            if (categoryId <= GetCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORY);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblProductCategoryList(System.Collections.Generic.List<Int32> categoryIdListToDelete)
        {
            //Validate Input
            foreach (Int32 categoryId in categoryIdListToDelete)
                if (categoryId <= GetCategoryIdMinValue)
                    throw (new ArgumentOutOfRangeException("categoryId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(categoryIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CategoryIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORYLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == categoryIdListToDelete.Count ? true : false);
        }

        public static Category GetTblProductCategoryByCategoryId(Int32 categoryId)
        {
            if (categoryId <= GetCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYCATEGORYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<Category> GetTblProductCategoryByParentCategoryId(Int32 parentCategoryId)
        {
            if (parentCategoryId <= GetParentCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("parentCategoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection;
            else
                return null;
        }

        public static List<Category> GetTblProductCategoryByParentCategoryIdPaged(Int32 parentCategoryId, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (parentCategoryId <= GetParentCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("parentCategoryId"));

            if (orderBy == "")
                orderBy = "ParentCategoryId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static int GetTblProductCategoryByParentCategoryIdPagedCount(Int32 parentCategoryId, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (parentCategoryId <= GetParentCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("parentCategoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetAllTblProductCategory()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORY);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<Category> GetAllTblProductCategoryPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "CategoryId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblProductCategoryPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetProductCategoryByCondition(int howMany, bool isActive, bool isDeleted, bool isFeatured)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 40, ParameterDirection.Input, howMany);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 1, ParameterDirection.Input, isActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 1, ParameterDirection.Input, isDeleted);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 1, ParameterDirection.Input, isFeatured);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_TBL_PRODUCT_CATEGORY_BY_CONDITION);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static List<Category> GetActiveSubCategoriesByParentCategory(int parentCategoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 40, ParameterDirection.Input, parentCategoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static List<Category> GetActiveCategoriesByCount(int howMany)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@howMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_COUNT);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetProductCategoryCountByName(string CategoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetProductCategoryCountByNameAndParent(int parentCatgoryId, string CategoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCatgoryId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME_AND_PARENT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetProductCategoryByPrefix(string prefix)
        {
            if (string.IsNullOrEmpty(prefix))
                throw (new ArgumentOutOfRangeException("prefix"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Prefix", SqlDbType.NVarChar, 50, ParameterDirection.Input, prefix);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PREFIX);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Category> GetNonDeletedSubCategoriesByParentCategoryIdPaged(int parentId, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentId);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedSubCategoriesByParentCategoryIdCount(int parentId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetNonDeletedSubCategoriesPaged(string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedCategoriesCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetAllCategoriesByNamePaged(string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllCategoriesByNameCount(string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetNonDeletedCategoriesByNamePaged(string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedCategoriesByNameCount(string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
        public static List<Category> GetAllCategoriesByParentCategoryIdAndNamePaged(int categoryId, string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentcategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllCategoriesByParentCategoryIdAndNameCount(int parentCategoryId, string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@parentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Category> GetNonDeletedCategoriesByParentCategoryIdAndNamePaged(int categoryId, string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentcategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedCategoriesByParentCategoryIdAndNameCount(int parentCategoryId, string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static void IncreaseSubCategoryCount(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_INCREASE_SUB_CATEGORY_COUNT);
            ExecuteScalarCmd(sqlCmd);
        }

        public static void DecreaseSubCategoryCount(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_DECREASE_SUB_CATEGORY_COUNT);
            ExecuteScalarCmd(sqlCmd);
        }

        public static Category GetTblProductCategoryByName(string category)
        {
            if (string.IsNullOrEmpty(category))
                throw (new ArgumentOutOfRangeException("category"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, category);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_NAME);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static Category GetTblProductCategoryByUrl(string url)
        {
            url = "~/" + url;
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryURL", SqlDbType.NVarChar, 500, ParameterDirection.Input, url);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductCategory_GetTblProductCategoryByUrl");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }


        public static Category GetTblProductCategoryByName(int parentCategory, string subCategory)
        {
            if (string.IsNullOrEmpty(subCategory))
                throw (new ArgumentOutOfRangeException("subCategory"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategory);
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, subCategory);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PARENT_AND_SUBCATEGORY_NAME);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static int UpdateTblProductCategoryUrl(string oldCategoryUrl, string newCategoryUrl)
        {
            if (string.IsNullOrEmpty(oldCategoryUrl) || string.IsNullOrEmpty(newCategoryUrl))
                throw (new ArgumentOutOfRangeException("UpdateTblProductCategoryUrl"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@OldCategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, oldCategoryUrl);
            AddParameterToSqlCmd(sqlCmd, "@NewCategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newCategoryUrl);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_UPDATECATEGORYURL);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));
        }

        public static bool Save(Category category)
        {
            if (category.Id <= GetCategoryIdMinValue)
            {
                Int32 TempId = CreateNewTblProductCategory(category);
                if (TempId > 0)
                {
                    category.Id = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblProductCategory(category));
            }

        }

        public static Int32 CreateNewTblProductCategory(Category newTblProductCategory)
        {
            // validate Parameters
            if (newTblProductCategory == null)
                throw (new ArgumentNullException("newTblProductCategory"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@CategoryTitle", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryTitle);
            AddParameterToSqlCmd(sqlCmd, "@CategoryDescription", SqlDbType.NVarChar, 4000, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@CategoryImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@CategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryUrl);
            AddParameterToSqlCmd(sqlCmd, "@DisplayOrder", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.DisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParameterToSqlCmd(sqlCmd, "@SubCategoryCount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.SubCategoryCount);
            AddParameterToSqlCmd(sqlCmd, "@ProductCount", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.ParentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ProductDisplayOrder", SqlDbType.NVarChar, 500, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@CategoryBannerImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryBannerImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_CREATENEWTBLPRODUCTCATEGORY);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblProductCategory(Category newTblProductCategory)
        {
            // validate Parameters
            if (newTblProductCategory == null)
                throw (new ArgumentNullException("newTblProductCategory"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.Id);
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@CategoryTitle", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryTitle);
            AddParameterToSqlCmd(sqlCmd, "@CategoryDescription", SqlDbType.NVarChar, 4000, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@CategoryImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@CategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryUrl);
            AddParameterToSqlCmd(sqlCmd, "@DisplayOrder", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.DisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParameterToSqlCmd(sqlCmd, "@SubCategoryCount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.SubCategoryCount);
            AddParameterToSqlCmd(sqlCmd, "@ProductCount", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.ParentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ProductDisplayOrder", SqlDbType.NVarChar, 500, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@CategoryBannerImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryBannerImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_UPDATETBLPRODUCTCATEGORY);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblProductCategoryCollectionFromReader(IDataReader returnData)
        {

            //creating the instance of Employee collection
            List<Category> colTblProductCategory = new List<Category>();

            while (returnData.Read())
            {
                //passing the TblProductCategory constructor parameters from the current instance of data reader fields.
                Category newTblProductCategory = new Category()
                {
                    Id = returnData["CategoryId"] == System.DBNull.Value ? GetCategoryIdMinValue : (Int32)returnData["CategoryId"],
                    CategoryName = returnData["CategoryName"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryName"],
                    CategoryTitle = returnData["CategoryTitle"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryTitle"],
                    CategoryDescription = returnData["CategoryDescription"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryDescription"],
                    CategoryImageUrl = returnData["CategoryImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryImageUrl"],
                    CategoryUrl = returnData["CategoryUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryUrl"],
                    DisplayOrder = returnData["DisplayOrder"] == System.DBNull.Value ? 0 : (int)returnData["DisplayOrder"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    SubCategoryCount = returnData["SubCategoryCount"] == System.DBNull.Value ? 0 : (Int32)returnData["SubCategoryCount"],
                    ParentCategoryId = returnData["ParentCategoryId"] == System.DBNull.Value ? 0 : (Int32)returnData["ParentCategoryId"],
                    CategoryBannerImageUrl = returnData["CategoryBannerImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryBannerImageUrl"]
                };
                //adding the Employee to the collection
                colTblProductCategory.Add(newTblProductCategory);
            }

            //returns the collection of Companys 
            return (colTblProductCategory);
        }

        public static Int32 GetCategoryIdMinValue
        {
            get
            {
                return 0;
            }
        }
        public static Int32 GetParentCategoryIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SP_TBLPRODUCTCATEGORY_CREATENEWTBLPRODUCTCATEGORY = "sp_TblProductCategory_CreateNewTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_UPDATETBLPRODUCTCATEGORY = "sp_TblProductCategory_UpdateTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORY = "sp_TblProductCategory_DeleteTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORYLIST = "sp_TblProductCategory_DeleteTblProductCategoryList";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYCATEGORYID = "sp_TblProductCategory_GetTblProductCategoryByCategoryId";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYID = "sp_TblProductCategory_GetTblProductCategoryByParentCategoryId";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGED = "sp_TblProductCategory_GetTblProductCategoryByParentCategoryIdPaged";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGEDCOUNT = "sp_TblProductCategory_GetTblProductCategoryByParentCategoryIdPagedCount";
        internal const string SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORY = "sp_TblProductCategory_GetAllTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGED = "sp_TblProductCategory_GetAllTblProductCategoryPaged";
        internal const string SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGEDCOUNT = "sp_TblProductCategory_GetAllTblProductCategoryPagedCount";

        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PARENT_AND_SUBCATEGORY_NAME = "sp_TblProductCategory_Get_ProductCategory_By_Parent_And_SubCategory_Name";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_NAME = "sp_TblProductCategory_Get_ProductCategory_By_Name";
        private const string SP_TBL_PRODUCTCATEGORY_GET_TBL_PRODUCT_CATEGORY_BY_CONDITION = "sp_TblProductCategory_Get_ProductCategory_By_Condition";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID = "sp_TblProductCategory_Get_Active_SubCategories_By_ParentCategoryId";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_COUNT = "sp_TblProductCategory_Get_Active_Categories_By_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_ROOT_CATEGORIES = "sp_TblProductCategory_Get_Active_RootCategories";
        private const string SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME = "sp_TblProductCategory_Get_Category_Count_By_Name";
        private const string SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME_AND_PARENT = "sp_TblProductCategory_Get_Category_Count_By_Name_And_ParentId";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PREFIX = "sp_TblProductCategory_Get_Active_Categories_By_Prefix";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED = "sp_TblProductCategory_Get_NonDeleted_SubCategories_By_ParentCategoryIdPaged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_COUNT = "sp_TblProductCategory_Get_NonDeleted_SubCategories_By_ParentCategoryIdCount";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_PAGED = "sp_TblProductCategory_Get_NonDeleted_SubCategories_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_COUNT = "sp_TblProductCategory_Get_NonDeleted_SubCategories_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_PAGED = "sp_TblProductCategory_Get_NonDeleted_Categories_By_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_COUNT = "sp_TblProductCategory_Get_NonDeleted_Categories_By_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED = "sp_TblProductCategory_Get_NonDeleted_Categories_By_ParentCategoryId_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT = "sp_TblProductCategory_Get_NonDeleted_Categories_By_ParentCategoryId_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_PAGED = "sp_TblProductCategory_Get_All_Categories_By_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_COUNT = "sp_TblProductCategory_Get_All_Categories_By_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED = "sp_TblProductCategory_Get_All_Categories_By_ParentCategoryId_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT = "sp_TblProductCategory_Get_All_Categories_By_ParentCategoryId_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_INCREASE_SUB_CATEGORY_COUNT = "sp_TblProductCategory_Increase_Sub_Category_Count";
        private const string SP_TBL_PRODUCTCATEGORY_DECREASE_SUB_CATEGORY_COUNT = "sp_TblProductCategory_Decrease_Sub_Category_Count";
        private const string SP_TBL_PRODUCTCATEGORY_UPDATECATEGORYURL = "sp_TblProductCategory_UpdateCategoryUrl";


        public static List<Category> GetAllTblPartnerCategoryByPartnerId(int partnerId)
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@PartnerId", SqlDbType.Int, 4, ParameterDirection.Input, partnerId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductCategory_GetTblProductCategoryByPartnerId");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(CategoryDAO.GenerateTblProductCategoryCollectionFromReader);
            List<Category> objCollection = ((List<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static Int32 CreateNewTblPartnerCategories
        (
            Int32 partnerId,
            Int32 categoryId
        )
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@PartnerId", SqlDbType.Int, 0, ParameterDirection.Input, partnerId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERCATEGORIES_CREATENEWTBLPARTNERCATEGORIES);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));
        }

        public static bool DeleteTblPartnerCategoriesByCategoryId
        (
            Int32 categoryId
        )
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblPartnerCategories_DeleteTblPartnerCategoriesByCategoryId");
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        internal const string SPR_TBLPARTNERCATEGORIES_CREATENEWTBLPARTNERCATEGORIES = "spr_TblPartnerCategories_CreateNewTblPartnerCategories";

    }
}
